﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class InvestigationTypeBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);




        public void insert(string inv_type_name, string record_status,int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@inv_type_name", inv_type_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@created_by", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Today);
           
  
                string sql;



                sql = " insert into hrm_dc_investigation_types(inv_type_name,record_status,created_by,created_when) "
+ " values(@inv_type_name,@record_status,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }



        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
                        + " hrm_dc_investigation_types.inv_type_name,"
                        + " mas_record_statuses.record_status_name as status,"
                        + " hrm_dc_investigation_types.record_status,"
                        + " hrm_dc_investigation_types.inv_type_seq"
                        + " FROM"
                        + " hrm_dc_investigation_types"
                        + " Inner Join mas_record_statuses ON mas_record_statuses.record_status = hrm_dc_investigation_types.record_status"
                        + " where hrm_dc_investigation_types.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";
                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable ListInvest()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
                        + " hrm_dc_investigation_types.inv_type_name,"
                        + " mas_record_statuses.record_status_name as status,"
                        + " hrm_dc_investigation_types.record_status,"
                        + " hrm_dc_investigation_types.inv_type_seq"
                        + " FROM"
                        + " hrm_dc_investigation_types"
                        + " Inner Join mas_record_statuses ON mas_record_statuses.record_status = hrm_dc_investigation_types.record_status"
                        + " where hrm_dc_investigation_types.record_status not like '" + SystemInfo._DeleteRecordStatus + "'"
                        + " AND hrm_dc_investigation_types.record_status not like '" + SystemInfo._CancelRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        //public DataTable ListInvest1(int Seq)
        //{
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@sequence", Seq);
        //        string sql;
        //        sql = " SELECT "
        //                + " hrm_dc_investigation_types.inv_type_name,"
        //                + " mas_record_statuses.record_status_name as status,"
        //                + " hrm_dc_investigation_types.record_status,"
        //                + " hrm_dc_investigation_types.inv_type_seq"
        //                + " FROM"
        //                + " hrm_dc_investigation_types"
        //                + " Inner Join mas_record_statuses ON mas_record_statuses.record_status = hrm_dc_investigation_types.record_status"
        //                + " where hrm_dc_investigation_types.record_status not like '" + SystemInfo._DeleteRecordStatus + "'"
        //                + " AND hrm_dc_investigation_types.record_status not like '" + SystemInfo._CancelRecordStatus + "'"

        //                + " UNION "

        //                + " SELECT "
        //                  + " it.inv_type_name, "
        //                  + "	r.record_status_name AS status, "
        //                  + " it.record_status, "
        //                  + "	it.inv_type_seq "
        //                + " FROM "
        //                  + "	hrm_ps_commands c "
        //                + " INNER JOIN hrm_dc_investigation_types it ON c.inv_type_seq = it.inv_type_seq "
        //                + " INNER JOIN mas_record_statuses r ON r.record_status = it.record_status "
        //                + " WHERE "
        //                    + " it.record_status = 'C' AND c.cmd_seq = @sequence ";


        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //}


        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }



        public void Update(int inv_type_seq,string inv_type_name, string record_status, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@inv_type_name", inv_type_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@inv_type_seq", inv_type_seq);
                dbManager.AddParameters(3, "@updated_by", updated_by);
                dbManager.AddParameters(4, "@updated_when", DateTime.Today);


                string sql;
            
                sql = " update hrm_dc_investigation_types "
         + "set inv_type_name=@inv_type_name,record_status=@record_status,updated_by=@updated_by,updated_when=@updated_when  "
         + " where inv_type_seq=@inv_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();

            }

        }


    

 
        public void Delete(int inv_type_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inv_type_seq", inv_type_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
            
                sql = " update hrm_dc_investigation_types set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where inv_type_seq = @inv_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }







     

   
    }
}
